package com.xiaowen.spring.dao;

import com.xiaowen.spring.model.Student;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * Created by 15c on 2017/10/27.
 */
@Repository("jdbcStudentDao")
public class IStudentDaoImpl implements IStudentDao {
    private JdbcTemplate jdbcTemplate;

    @Resource
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }


    @Override
    public int add(Student student) {
        int i = this.jdbcTemplate.update(
                "insert into student (sno, sname,ssex,sage,sdept,sid,saddress,stelephonenum) values (?,?,?,?,?,?,?,?)",
                student.getSno(),
                student.getSname(),
                student.getSsex(),
                student.getSage(),
                student.getSdept(),
                student.getSid(),
                student.getSaddress(),
                student.getStelephonenum());
        return i;
    }

    @Override
    public int update(Student student) {
        int i = this.jdbcTemplate.update("update student set sname = ?,ssex= ?,sage= ?,sdept= ?,sid= ?,saddress= ?,stelephonenum= ?  where sno = ?",

                student.getSname(),
                student.getSsex(),
                student.getSage(),
                student.getSdept(),
                student.getSid(),
                student.getSaddress(),
                student.getStelephonenum(),
                student.getSno());
        return i;
    }

    @Override
    public int delete(String sno) {
        int i = this.jdbcTemplate.update(
                "delete from student where sno = ?", sno);
        return i;
    }

    @Override
    public Student load(String sno) {

        String sql = "select * from student where sno = " + sno;
        Student student = this.jdbcTemplate.queryForObject(sql, new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet resultSet, int i) throws SQLException {
                Student student = new Student();
                student.setSno(sno);
                student.setSname(resultSet.getString("sname"));
                student.setSsex(resultSet.getString("ssex"));
                student.setSage(resultSet.getInt("sage"));
                student.setSdept(resultSet.getString("sdept"));
                student.setSid(resultSet.getString("sid"));
                student.setSaddress(resultSet.getString("saddress"));
                student.setStelephonenum(resultSet.getString("stelephonenum"));
                return student;

            }
        });

        return student;
    }

    @Override
    public Student load1(String sno) {
        String sql = "select * from student where sno =? ";
        Student student = this.jdbcTemplate.queryForObject(sql, new StudentRowMapper(), sno);
        return student;
    }
    //内部类封装
    class StudentRowMapper implements RowMapper<Student> {

        @Override
        public Student mapRow(ResultSet resultSet, int i) throws SQLException {
            Student student = new Student();
            student.setSno("sno");
            student.setSname(resultSet.getString("sname"));
            student.setSsex(resultSet.getString("ssex"));
            student.setSage(resultSet.getInt("sage"));
            student.setSdept(resultSet.getString("sdept"));
            student.setSid(resultSet.getString("sid"));
            student.setSaddress(resultSet.getString("saddress"));
            student.setStelephonenum(resultSet.getString("stelephonenum"));
            return student;
        }
    }
    //queryForList方法
    @Override
    public List<Student> list(String field, String keyword) {
        Student student = new Student();
        List<Student> studentList = new LinkedList<Student>();
        String sql;
        if (field == null || (keyword == null)) {
            sql = "select * from student";
        } else {
            sql = " select * from student where " + field + " like '%" + keyword + "%'";
        }
        //将查询出的数据存入list集合中
        List list = jdbcTemplate.queryForList(sql);
        //构建list集合迭代器遍历集合
        Iterator it = list.iterator();
        while (it.hasNext()) {

            //将每条记录存入map集合便于对象的封装
            Map studentMap = (Map) it.next();
            student.setSno((String) studentMap.get("sno"));
            student.setSname((String) studentMap.get("sname"));
            student.setSage((Integer) studentMap.get("sage"));
            student.setSsex((String) studentMap.get("ssex"));
            student.setSdept((String) studentMap.get("sdept"));
            student.setSaddress((String) studentMap.get("saddress"));
            student.setSid((String) studentMap.get("sid"));
            student.setStelephonenum((String) studentMap.get("stelephonenum"));
            studentList.add(student);
        }
        return studentList;
    }

    //jdbcTemplate.query方法
    @Override
    public List<Student> list1(String field, String keyword) {
        String sql;
        if (field == null || (keyword == null)) {
            sql = "select * from student";
        } else {
            sql = " select * from student where " + field + " like '%" + keyword + "%'";
        }
        List<Student> list = jdbcTemplate.query(sql, new StudentRowMapper(),9);
        return list;
    }


}